package com.labmi.experiment;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.FieldResult;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.OneToMany;
import javax.persistence.SqlResultSetMapping;

@Entity
@SqlResultSetMapping(
	    name="nativeQueryMapping",
	    entities = {
	        @EntityResult(
	            entityClass=ClothingItem.class,
	            fields = {
	                @FieldResult(name = "id", 			column = "clothingitem.id"),
	                @FieldResult(name = "name", 		column = "clothingitem.name"),
	                @FieldResult(name = "description", 	column = "clothingitem.description"),
	                @FieldResult(name = "price", 		column = "clothingitem.price"),
	                @FieldResult(name = "detailImage",	column = "clothingitem.detailImage"),
	                /*@FieldResult(name = "size", 		column = "clothingitem.size") ,
	                @FieldResult(name = "gender", 		column = "clothingitem.gender"),
	                @FieldResult(name = "category", 	column = "clothingitem.category")*/
	            }
	        )
	    }
	 )

@NamedNativeQueries({	 
	
	@NamedNativeQuery(	name="rootCategoryItemList" , 
		query="SELECT DISTINCT " +
				"clothingitem.id, 			clothingitem.name, clothingitem.description,  	clothingitem.price, " +
				"clothingitem.detailImage " +
		" FROM clothingitem join (" + 
		" SELECT root.id AS root_id, root.name AS root_name, secondCategory.id AS secondCategory_id, secondCategory.name AS secondCategory_name, thirdCategory.id AS thirdCategory_id, thirdCategory.name AS thirdCategory_name" +
		" FROM category AS root" +
		" LEFT OUTER JOIN category AS secondCategory ON secondCategory.parent = root.id" +
		" LEFT OUTER JOIN category AS thirdCategory ON thirdCategory.parent = secondCategory.id" +
		" WHERE root.parent =0) AS CAT_STRUCT on (category_id = CAT_STRUCT.thirdcategory_id AND " +
		"(CAT_STRUCT.root_id = ?1 OR    CAT_STRUCT.secondCategory_id = ?1 OR CAT_STRUCT.thirdCategory_id = ?1)" +
		")", 
		resultSetMapping="nativeQueryMapping" //SqlResultSetMapping
	),
	@NamedNativeQuery(	name="getItemsByCategoryAndKey" , 
			query="SELECT DISTINCT " +
					"clothingitem.id, 			clothingitem.name, clothingitem.description,  	clothingitem.price, " +
					"clothingitem.detailImage " +
			" FROM clothingitem join (" + 
			" SELECT root.id AS root_id, root.name AS root_name, secondCategory.id AS secondCategory_id, secondCategory.name AS secondCategory_name, thirdCategory.id AS thirdCategory_id, thirdCategory.name AS thirdCategory_name" +
			" FROM category AS root" +
			" LEFT OUTER JOIN category AS secondCategory ON secondCategory.parent = root.id" +
			" LEFT OUTER JOIN category AS thirdCategory ON thirdCategory.parent = secondCategory.id" +
			" WHERE root.parent =0) AS CAT_STRUCT on (category_id = CAT_STRUCT.thirdcategory_id AND " +
			"(CAT_STRUCT.root_id = ?1 OR    CAT_STRUCT.secondCategory_id = ?2 OR CAT_STRUCT.thirdCategory_id = ?3)" +
			") WHERE clothingitem.name like ?4 OR clothingitem.description like ?5 ", 
			resultSetMapping="nativeQueryMapping" //SqlResultSetMapping
		)

		
	}
	
	)
			
public class ClothingItem extends Item{

	/**
	 * 
	 */
	private static final long serialVersionUID = -139482730225163618L;

	@ManyToOne(cascade={ CascadeType.PERSIST, CascadeType.REFRESH, CascadeType.MERGE })
	private Category category;
	
	
	private String gender;
	
	private String brand;
	
	
	public ClothingItem() {
		// TODO Auto-generated constructor stub
	}
	
	public ClothingItem(String name, String description, int price, String image, String gender) {
		super(name, description, price, image);
		this.gender = gender;
	}
	
	


	public void setGender(String gender) {
		this.gender = gender;
	}


	public String getGender() {
		return gender;
	}

	public void setCategory(Category category) {
		this.category = category;
	}

	public Category getCategory() {
		return category;
	}

	public void setBrand(String brand) {
		this.brand = brand;
	}

	public String getBrand() {
		return brand;
	}

	
	
	
	
	
	
}
